package com.framework.utils.excel;

import com.framework.exception.RRException;
import com.sun.org.apache.regexp.internal.RE;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;

/**
 * excel工具类
 * @author wq
 */
public class ExcelUtil {
    /**
     * 浏览器下载excel
     * @param response response对象
     * @param fileName 文件名
     * @param wb workbook文件
     */
   public static void downLoadByExcel(HttpServletResponse response, String fileName, Workbook wb) {
        try {

            //web浏览通过MIME类型判断文件是excel类型
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf-8");
            //设置文件名编码，以防中文乱码
            fileName = URLEncoder.encode(fileName,"UTF-8");
            // Content-disposition属性设置成以附件方式进行下载
            response.setHeader("Content-disposition", "attachment;filename="+fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (UnsupportedEncodingException e) {
            throw new RRException("9000", "系统错误");
        } catch (IOException e) {
            throw new RRException("9000", "系统错误");
        }
    }

    /**
     * 从excel文件获取工作表
     * @param fileName 验证文件格式
     * @param file 文件
     * @param sheetIndex 工作表索引 从0开始
     * @return 工作表对象
     */
    public static Sheet getSheetFromFile(String fileName, MultipartFile file, Integer sheetIndex) {
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new RRException("9000", "文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        Workbook wb;
        try {
            InputStream is = file.getInputStream();
            if (isExcel2003) {

                wb = new HSSFWorkbook(is);

            } else {
                wb = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            throw new RRException("9000", e.getMessage());
        }
        Sheet sheet = wb.getSheetAt(sheetIndex);
        if (sheet == null) {
            throw new RRException("9000", "系统异常");
        }
        return sheet;
    }

    /**
     * 生成文件对象
     * @param sheetName 工作表名称
     * @param title 标题
     * @param content 内容
     * @return workbook对象
     */
    public static Workbook generateNewWorkbook(String sheetName,String [] title,String [][] content){

        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet(sheetName);

        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        Row row = sheet.createRow(0);

        // 创建单元格，并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();
        // 创建一个居中格式
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //声明列对象
        Cell cell;
        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }
        //创建内容
        for (int i = 0; i < content.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < content[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(content[i][j]);
            }
        }
        return wb;
    }

}
